﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraReports.UI;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_TOOL.Common;
using ERP_RORZE_TOOL.DBAccess;


namespace ERP_RORZE_PRODUCT.Product.Incauhinh
{
    public partial class ucInLenh : DevExpress.XtraEditors.XtraUserControl
    {

        public ucInLenh()
        {
            InitializeComponent();
            BindDataGrid();

        }

        private void BindDataGrid()
        {
            DataTable dt = BaseDAO.ExecuteQuery("Select * from [242_InLenhMONo]");
            gridControl1.DataSource = dt;
            gridView1.BestFitColumns();
        }

        private void btView_Click_1(object sender, EventArgs e)
        {
            Cursor.Current = Cursors.WaitCursor;

            List<Utilities.ListItem> partID = new List<Utilities.ListItem>();
            DataTable dtMoList = new DataTable();
            DataSet dsInlenh = new DataSet();
            DataTable dtMaster = new DataTable();
            DataTable dtSubDetail = new DataTable();

            DataColumn colMaster = new DataColumn("Key", typeof(Int32));
            colMaster.AllowDBNull = false;
            //colMaster.Unique = true;

            
            //colSub.Unique = true;

            //string mOnOwhere = " [MachStatusPrint].[MONo] = N''M570390'' or [MachStatusPrint].MONo = N''M575685'' ";
            string mOnOwhere = string.Empty;
            string partIdwhere = string.Empty;
            string sqlSubDetail1;

            dtMoList = BaseDAO.ExecuteQuery("Select * from [242_InLenhMONo]");
            if (dtMoList.Rows.Count > 0)
            {
                foreach (DataRow row in dtMoList.Rows)
                {
                    mOnOwhere += " [MachStatusPrint].[MONo] = N''" + row["MONo"] + "'' or";
                }
            }
            else
            {
                MessageBox.Show("No data. Please check your MO Number.", "Message");
                return;
            }

            string sqlMaster1 = "Sp_242_InLenh N'" + mOnOwhere.Substring(0, mOnOwhere.Length - 3) + "'";
            dtMaster = BaseDAO.ExecuteQuery(sqlMaster1);
            dtMaster.Columns.Add(colMaster);

            if (dtMaster.Rows.Count > 0)
            {
                int i = 0;
                foreach (DataRow row in dtMaster.Rows)
                {
                    row["Key"] = i;
                    //partIdwhere += " [PartID] = N''" + row["PartID"] + "'' or";
                    //partId.Add(row["PartID"].ToString());
                    partID.Add(new Utilities.ListItem(i.ToString(), row["PartID"].ToString()));
                    i++;
                }
            }
            else
            {
                MessageBox.Show("No data. Please check your MO Number.", "Message");
                return;
            }

            foreach (Utilities.ListItem s in partID)
            {
                string sql1 = " [PartID] = N''" + s.Value + "''";
                string sql = "Sp_242_InLenh_OptionData N'" + sql1 + "'";
                DataTable dtSub = BaseDAO.ExecuteQuery(sql);

                DataColumn colSub = new DataColumn("Key", typeof(Int32));
                colSub.AllowDBNull = false;

                dtSub.Columns.Add(colSub);
                foreach (DataRow row in dtSub.Rows)
                {
                    row["Key"] = int.Parse(s.ID);
                }

                dtSubDetail.Merge(dtSub);
                //dtSubDetail.Columns.Add(colSub);
            }

            dsInlenh.Tables.Add(dtMaster);
            dsInlenh.Tables.Add(dtSubDetail);


            DataColumn keyColumn = dtMaster.Columns["Key"];
            DataColumn foreignKeyColumn = dtSubDetail.Columns["Key"];
            dsInlenh.Relations.Add("DetailRelation", keyColumn, foreignKeyColumn);
            ucInLenhView inLenhView = new ucInLenhView(dsInlenh);
            panelBottom.Controls.Clear();
            panelBottom.Controls.Add(inLenhView);
            inLenhView.Dock = DockStyle.Fill;

            //Rp_InLenh100 inlenh = new Rp_InLenh100(dsInlenh);
            //inlenh.CreateDocument();
            //inlenh.ShowPreview();

            Cursor.Current = Cursors.Default;
        }

        private void btImport_Click(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);

                    SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("MONo", "MONo");
                    bulkInsert.ColumnMappings.Add(date);

                    bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_InLenhMONo]";
                    bulkInsert.WriteToServer(dr);


                    connection.Close();
                    MessageBox.Show("Import data successfully");
                    BindDataGrid();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            if (gridView1.SelectedRowsCount > 0)
            {
                var row = new DataRow[gridView1.SelectedRowsCount];
                var moNo = new string[gridView1.SelectedRowsCount];

                for (int i = 0; i < gridView1.SelectedRowsCount; i++)
                {
                    row[i] = gridView1.GetDataRow(gridView1.GetSelectedRows()[i]);
                    moNo[i] = row[i]["ID"].ToString();
                }


                if (MessageBox.Show("Do you want to delete this item(s)", "Warning",
                                MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
                {
                    for (int i = 0; i < moNo.Length; i++)
                    {
                        string sql = "Delete from [242_InLenhMONo] where [ID]= " + moNo[i];
                        if (BaseDAO.ExecuteNonQuery(sql) > 0)
                        {
                            // _action = 2;
                        }
                        else
                        {
                            MessageBox.Show("Error occurred when deleting data. Please try again.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }

                    BindDataGrid();

                }
            }
            else
            {
                MessageBox.Show("No data to edit/delete.", "Message", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }
        }

        private void gridView1_CustomDrawEmptyForeground(object sender, DevExpress.XtraGrid.Views.Base.CustomDrawEventArgs e)
        {
            if (gridView1.RowCount != 0)
                return;

            StringFormat drawFormat = new StringFormat();

            drawFormat.Alignment = drawFormat.LineAlignment = StringAlignment.Center;

            e.Graphics.DrawString("No items exist in this view", e.Appearance.Font, SystemBrushes.ControlDark, new RectangleF(e.Bounds.X, e.Bounds.Y, e.Bounds.Width, e.Bounds.Height), drawFormat);

        }

        private void gridView1_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.Alt)
            {
                if (e.KeyCode == Keys.E)
                {
                    if (gridView1.OptionsBehavior.Editable)
                    {
                        //gridView1.fo;
                        gridView1.OptionsBehavior.Editable = false;
                    }
                    else
                    {
                        gridView1.OptionsBehavior.Editable = true;
                        gridView1.Columns["ID"].OptionsColumn.AllowEdit = false;
                        gridView1.Columns["ID"].Visible = false;
                    }
                }
            }
            else if (e.Control)
            {
                if (e.KeyCode == Keys.N)
                {
                    gridView1.AddNewRow();
                }
            }
        }

        private void gridView1_ValidateRow(object sender, DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs e)
        {
            GridView view = sender as GridView;


            //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
            if (view.IsNewItemRow(e.RowHandle))
            {
                string insert = "insert into [242_InLenhMONo] ([MONo])" +
                                        " VALUES(N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["MONo"]).ToString() + "' )";
                BaseDAO.ExecuteNonQuery(insert);

            }
            //Cũ thì update
            else
            {
                string mONo = (view.GetRowCellValue(e.RowHandle, view.Columns["MONo"]).ToString() == "") ? "null" : "'" + view.GetRowCellValue(e.RowHandle, view.Columns["MONo"]).ToString() + "'";
                string id = (view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString() == "") ? "null" : "'" + view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString() + "'";

                string update = "update [242_InLenhMONo] set [MONo]=" + mONo
                    + ", [MONo] = " + mONo
                    + " where [ID] = " + id;
                BaseDAO.ExecuteNonQuery(update);

            }
        }

        private void btNewRow_Click(object sender, EventArgs e)
        {
            gridView1.AddNewRow();
        }
    }

    #region ObjectInLenh Class
    public class ObjectInLenh
    {
        private string topLeft;
        private string sokehoach;
        private string orderPKD;
        private string sLCan;
        private string SLLenh;
        private string _thanLenh;
        private string _kichThuocPhoi;
        private string _loaiPhoi;
        private string _coCat;
        private string _gcongTho;

        private string PartID;
        private string _mono;

        private string _thoiHanGcong;
        private string _longRen;
        private string _phunCat;
        private string _ngayIn;
        private string _chuY;
        private string _solenhnhapkho;


        private DataTable optionData;
        public string Sokehoach
        {
            get { return sokehoach; }
            set { sokehoach = value; }
        }

        public string OrderPkd
        {
            get { return orderPKD; }
            set { orderPKD = value; }
        }

        public string SLCan
        {
            get { return sLCan; }
            set { sLCan = value; }
        }

        public string SlLenh
        {
            get { return SLLenh; }
            set { SLLenh = value; }
        }

        public string ThanLenh
        {
            get { return _thanLenh; }
            set { _thanLenh = value; }
        }

        public string KichThuocPhoi
        {
            get { return _kichThuocPhoi; }
            set { _kichThuocPhoi = value; }
        }

        public string LoaiPhoi
        {
            get { return _loaiPhoi; }
            set { _loaiPhoi = value; }
        }

        public string CoCat
        {
            get { return _coCat; }
            set { _coCat = value; }
        }

        public string GcongTho
        {
            get { return _gcongTho; }
            set { _gcongTho = value; }
        }

        public string PartId
        {
            get { return PartID; }
            set { PartID = value; }
        }

        public string Mono
        {
            get { return _mono; }
            set { _mono = value; }
        }

        public string ThoiHanGcong
        {
            get { return _thoiHanGcong; }
            set { _thoiHanGcong = value; }
        }

        public string LongRen
        {
            get { return _longRen; }
            set { _longRen = value; }
        }

        public string PhunCat
        {
            get { return _phunCat; }
            set { _phunCat = value; }
        }

        public string NgayIn
        {
            get { return _ngayIn; }
            set { _ngayIn = value; }
        }

        public string ChuY
        {
            get { return _chuY; }
            set { _chuY = value; }
        }

        public string Solenhnhapkho
        {
            get { return _solenhnhapkho; }
            set { _solenhnhapkho = value; }
        }

        public DataTable OptionData
        {
            get { return optionData; }
            set { optionData = value; }
        }

        public string TopLeft
        {
            get { return topLeft; }
            set { topLeft = value; }
        }
    }
    #endregion
}
